package com.wanmait.hotelManage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.News;
import com.wanmait.hotelManage.vo.Pager;

public class NewsDAO {
	private NewsDAO() {};
	private static NewsDAO newsDAO=new NewsDAO();
	public static NewsDAO getNewsDAO() {
		return newsDAO;
	}
	/**
	 * 查找全部
	 * @param pager
	 * @return
	 */
	public List<News>findall(Pager pager){
		List<News>news=new ArrayList<News>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT * FROM news where enable=1 order by input_time desc limit ?,?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			News n=null;
			while(rs.next()) {
				n=new News();
				n.setId(rs.getInt("id"));
				n.setIntr(rs.getString("intr"));
				n.setTitle(rs.getString("title"));
				n.setContent(rs.getString("content"));
				n.setInputTime(rs.getTimestamp("input_time"));
				n.setPic(rs.getString("pic"));
				news.add(n);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return news;
	}
	/**
	 * 求总行数
	 * @return
	 */
	public int getrows(){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) c from news where enable=1 ");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			rs.next();
			Integer count=rs.getInt("c");
			return count;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
//	public static void main(String[] args) {
//		List<News>n=NewsDAO.getNewsDAO(
//	}
	/**
	 * 删除
	 * @param parseInt
	 */
	public void de(int parseInt) {
		String sql="update news set enable=0 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, parseInt);
	}
//	/**
//	 * 通过
//	 */
//	public void by(int id) {
//		String sql="update news set news.read=1 where id=?";
//		DBHelper.getDBHelper().executeUpdate(sql, id);
//	}
//	/**
//	 * 批量通过
//	 * @param id
//	 */
//	public void bys(String id) {
//		String sql="update news set news.read=1 where id in("+id+")";
//		DBHelper.getDBHelper().executeUpdate(sql);
//	}
	/**
	 * 批量删除
	 * @param id
	 */
	public void del(String id) {
		String sql="update news set enable=0 where id in("+id+")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
	public void insert(News news) {
		String sql="insert into news(pic,content,title,intr) values(?,?,?,?)";
		Object[]params= {
				news.getPic(),
				news.getContent(),
				news.getTitle(),
				news.getIntr()
		};
		DBHelper.getDBHelper().executeUpdate(sql, params);
	}
	/**
	 *搜索的行数
	 * @param text
	 * @return
	 */
	public Integer getrowss(String text) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) c from news where enable=1 and news.title like '%"+text+"%'");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			rs.next();
			Integer count=rs.getInt("c");
			return count;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
	public static void main(String[] args) {
		int rows=NewsDAO.getNewsDAO().getrowss("1");
		System.out.println(rows);
	}
	public List<News> findtitle(String text, Pager pager) {
		List<News>news=new ArrayList<News>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT * FROM news where enable=1 and news.title like '%"+text+"%' order by input_time desc limit ?,?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			News n=null;
			while(rs.next()) {
				n=new News();
				n.setId(rs.getInt("id"));
				n.setIntr(rs.getString("intr"));
				n.setTitle(rs.getString("title"));
				n.setContent(rs.getString("content"));
				n.setInputTime(rs.getTimestamp("input_time"));
				n.setPic(rs.getString("pic"));
				news.add(n);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return news;
	}
	public News findid(Integer id) {
		News n=null;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT * FROM news where enable=1 and id=?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()) {
				n=new News();
				n.setId(rs.getInt("id"));
				n.setIntr(rs.getString("intr"));
				n.setTitle(rs.getString("title"));
				n.setContent(rs.getString("content"));
				n.setInputTime(rs.getTimestamp("input_time"));
				n.setPic(rs.getString("pic"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return n;
	}
//	public static void main(String[] args) {
//		News n=NewsDAO.getNewsDAO().findid(1);
//		System.out.println(n.getContent());
//	}
}
